package com.chengxiaoxiao.dao.impl;

import com.chengxiaoxiao.dao.BookDao;
import com.chengxiaoxiao.domain.Books;
import com.chengxiaoxiao.domain.Category;
import com.chengxiaoxiao.domain.Publisher;
import com.chengxiaoxiao.utils.db.*;

import java.util.ArrayList;
import java.util.List;


/*`id` int(11) NOT NULL AUTO_INCREMENT,
  `bookName` varchar(100) NOT NULL,
  `author` varchar(32) NOT NULL,
  `subTime` datetime DEFAULT NULL,
  `publisherId` int(11) DEFAULT NULL,
  `number` int(11) NOT NULL,
  `descript` varchar(256) DEFAULT NULL,
  `price` float(8,2) NOT NULL,
  `category_Id` int(11) DEFAULT NULL,
  `isbn` varchar(64) DEFAULT NULL,
  */

public class BookDaoImpl implements BookDao {

    private DBAssist da = new DBAssist(DBCPUtil.getDataSource());

    @Override
    public void add(Books books) {
        da.update("insert into books(bookName,author,subTime,publisherId,number,descript,price,category_Id,isbn) values(?,?,?,?,?,?,?,?,?)",
                books.getBookName(), books.getAuthor(), books.getSubTime(), books.getPublisherId(), books.getNumber(), books.getDescript(), books.getPrice(), books.getCategory_Id(), books.getIsbn());
    }

    @Override
    public void update(Books books) {
        da.update("update books set bookName=?,author=?,subTime=?,publisherId=?,number=?,descript=?,price=?,category_Id=?,isbn=? where id=?",
                books.getBookName(), books.getAuthor(), books.getSubTime(), books.getPublisherId(), books.getNumber(), books.getDescript(), books.getPrice(), books.getCategory_Id(), books.getIsbn(), books.getId());

    }

    @Override
    public void delete(long id) {
        da.update("delete from books where id=?", id);
    }

    @Override
    public List get() {


        return null;
    }

    @Override
    public Books getbyId(long id) {

        String sql = "select * from books where id=?";
        Books book = (Books) da.query(sql, new BeanHandler(Books.class), id);

        if (book != null) {
            Category category = (Category) da.query("select * from category where id=?", new BeanHandler(Category.class), book.getCategory_Id());
            book.setCategory(category);

            Publisher publisher = (Publisher) da.query("select * from publisher where id=?", new BeanHandler(Publisher.class), book.getPublisherId());
            book.setPublisher(publisher);

        }
        return book;
    }

    @Override
    public List getListPage(int startIndex, int pageSize) {
        String sql = "select * from books limit ?,?";
        List<Books> list = (List<Books>) da.query(sql, new BeanListHandler(Books.class), startIndex, pageSize);
        List<Books> model = new ArrayList<>();
        for (int i = 0; i < list.size(); i++) {
            Books book = list.get(i);
            if (book != null) {
                Category category = (Category) da.query("select * from category where id=?", new BeanHandler(Category.class), book.getCategory_Id());
                book.setCategory(category);

                Publisher publisher = (Publisher) da.query("select * from publisher where id=?", new BeanHandler(Publisher.class), book.getPublisherId());
                book.setPublisher(publisher);
            }
            model.add(book);
        }

        return model;
    }

    @Override
    public int getTotalRecordsNum() {
        return (int) da.query("select COUNT(*) from books", new ScalarHandler());
    }

    @Override
    public List getPageLikeSNum(int startIndex, int pageSize, String bookName) {
        String sql = "select * from books where bookName like? limit ?,?";
        List<Books> list = (List<Books>) da.query(sql, new BeanListHandler(Books.class), "%" + bookName + "%", startIndex, pageSize);
        List<Books> model = new ArrayList<>();
        for (int i = 0; i < list.size(); i++) {
            Books book = list.get(i);
            if (book != null) {
                Category category = (Category) da.query("select * from category where id=?", new BeanHandler(Category.class), book.getCategory_Id());
                book.setCategory(category);

                Publisher publisher = (Publisher) da.query("select * from publisher where id=?", new BeanHandler(Publisher.class), book.getPublisherId());
                book.setPublisher(publisher);
            }
            model.add(book);
        }

        return model;

    }

    @Override
    public int getTotalRecordsNumByBookName(String bookName) {
        return (int) da.query("select COUNT(*) from books where bookName like?", new ScalarHandler(),"%"+bookName+"%");

    }


    @Override
    public List<Books> getBooksByCategory(String id) {
        String sql = "select * from books where category_Id=?";
        List<Books> list = (List<Books>) da.query(sql, new BeanListHandler(Books.class), id);
        List<Books> model = new ArrayList<>();
        for (int i = 0; i < list.size(); i++) {
            Books book = list.get(i);
            if (book != null) {
                Category category = (Category) da.query("select * from category where id=?", new BeanHandler(Category.class), book.getCategory_Id());
                book.setCategory(category);

                Publisher publisher = (Publisher) da.query("select * from publisher where id=?", new BeanHandler(Publisher.class), book.getPublisherId());
                book.setPublisher(publisher);
            }
            model.add(book);
        }
        return model;

    }

    @Override
    public int getBooksByCategoryPageNumber(String id) {
        return (int) da.query("select COUNT(*) from books where category_Id=?", new ScalarHandler(),id);

    }

    @Override
    public List getBooksByCategoryPage(int startIndex, int pageSize, String id) {
        String sql = "select * from books where category_Id=? limit ?,?";
        List<Books> list = (List<Books>) da.query(sql, new BeanListHandler(Books.class), id, startIndex, pageSize);
        List<Books> model = new ArrayList<>();
        for (int i = 0; i < list.size(); i++) {
            Books book = list.get(i);
            if (book != null) {
                Category category = (Category) da.query("select * from category where id=?", new BeanHandler(Category.class), book.getCategory_Id());
                book.setCategory(category);

                Publisher publisher = (Publisher) da.query("select * from publisher where id=?", new BeanHandler(Publisher.class), book.getPublisherId());
                book.setPublisher(publisher);
            }
            model.add(book);
        }

        return model;

    }

    @Override
    public Books getBookByIsbn(String isbn) {
        String sql = "select * from books where isbn=?";
        Books book = (Books) da.query(sql, new BeanHandler(Books.class), isbn);

        if (book != null) {
            Category category = (Category) da.query("select * from category where id=?", new BeanHandler(Category.class), book.getCategory_Id());
            book.setCategory(category);

            Publisher publisher = (Publisher) da.query("select * from publisher where id=?", new BeanHandler(Publisher.class), book.getPublisherId());
            book.setPublisher(publisher);

        }
        return book;
    }
}
